package com.anda.logistics.dao.impl;

import java.util.List;

import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Component;

import com.anda.logistics.dao.DriverLocationDao;
import com.anda.logistics.model.DriverLocation;
import com.anda.logistics.model.DriverLocationRt;
import com.anda.logistics.model.TruckInfo;
import com.anda.logistics.util.JQGridPager;
import com.anda.logistics.vo.LocationStatVo;
import com.anda.logistics.vo.TruckInfoVo;

@Component
public class DriverLocationDaoImpl extends AndaBaseSuperDao implements DriverLocationDao {
	@Override
	public void save(DriverLocation location) {
		sessionFactory.getCurrentSession().save(location);
	}

	@Override
	public DriverLocationRt getLocationByMobileNo(String mobileNo) {
		return (DriverLocationRt) sessionFactory.getCurrentSession()
		.createQuery("from DriverLocationRt where number = :mobileNo order by getTime desc ")
		.setString("mobileNo", mobileNo)
		.setMaxResults(1)
		.uniqueResult();
	}

	@Override
	public void deleteRt(long id) {
		sessionFactory.getCurrentSession()
			.createQuery("delete from DriverLocationRt where id = :id ")
			.setLong("id", id)
			.executeUpdate();
	}

	@Override
	public void save(DriverLocationRt rt) {
		sessionFactory.getCurrentSession().save(rt);		
	}

	@Override
	public List<LocationStatVo> getStat(int level, String plateNo, String region) {
		StringBuffer sqlQuery = new StringBuffer();
		if (level == 1){
			sqlQuery.append("select b.region,'' as city,count(*) as cnt ")
		        .append("  from TruckInfo a, DriverLocationrt b ")
		        .append(" where a.id = b.id")
		        .append("   and a.plateNo like :plateNo ")
		        .append("   and ( b.region = :region or :region = '' )")
		        .append(" group by b.region ");
		} else {
			sqlQuery.append("select b.region,b.city,count(*) as cnt ")
		        .append("  from TruckInfo a, DriverLocationrt b ")
		        .append(" where a.id = b.id")
		        .append("   and a.plateNo like :plateNo ")
		        .append("   and ( b.region = :region or :region = '' )")
		        .append(" group by b.region,b.city ");
		}
		if (plateNo == null || plateNo.trim().length() == 0) {
			plateNo = "%";
		} else {
			plateNo = new StringBuffer().append(plateNo).append("%").toString();
		}
		if (region == null || region.trim().length() == 0) {
			region = "";
		}
		return sessionFactory.getCurrentSession()
				.createSQLQuery(sqlQuery.toString())
				.setString("plateNo", plateNo)
				.setString("region",region)
				.setResultTransformer(Transformers.aliasToBean(LocationStatVo.class))
				.list();
	}

	@Override
	public List<TruckInfo> getStat(String plateNo, String region, String city) {
		StringBuffer sqlQuery = new StringBuffer();
		sqlQuery.append("select a.*")
	        .append("  from TruckInfo a, DriverLocationrt b ")
	        .append(" where a.id = b.id")
	        .append("   and a.plateNo like :plateNo ")
	        .append("   and ( b.region = :region )")
	        .append("   and ( b.city = :city ) ");
		if (plateNo == null || plateNo.trim().length() == 0) {
			plateNo = "%";
		} else {
			plateNo = new StringBuffer().append(plateNo).append("%").toString();
		}
		return sessionFactory.getCurrentSession()
				.createSQLQuery(sqlQuery.toString())
				.setString("plateNo", plateNo)
				.setString("region",region)
				.setString("city", city)
				.setResultTransformer(Transformers.aliasToBean(TruckInfo.class))
				.list();
	}

	@Override
	public List<TruckInfo> getPagerTruckList(JQGridPager pager, String city,
			String region, String plateNo) {
		StringBuffer queryStr = new StringBuffer();
		queryStr.append("select a.id,a.truckBrand,a.truckType,a.capacity,a.plateNo,a.truckLength")
			.append("     ,a.truckVolumn,a.bodyStruc,a.locProvince,a.locCity,a.locRegion,a.drivingLicense")
			.append("     ,a.runningToken,a.driverName,a.driverSex,a.mobileNo,a.licenseType,a.freqLine")
			.append("     ,a.makeTime,a.modifyTime,a.truckState,a.createUserId,a.createUserName,a.auditUserId")
			.append("     ,a.auditUserName,a.auditTime")
			.append("     ,b.lat,b.lng,b.getTime,b.cityCode,b.addressDetail,b.locationDesc")
	        .append("  from TruckInfo a, DriverLocationRt b ")
	        .append(" where a.id = b.id")
	        .append("   and a.plateNo like :plateNo ")
	        .append("   and ( b.region = :region )")
	        .append("   and ( b.city = :city ) ")
			.append(" order by a.id ");
		if (plateNo == null || plateNo.trim().length() == 0) {
			plateNo = "%";
		} else {
			plateNo = new StringBuffer().append(plateNo).append("%").toString();
		}
		
		return sessionFactory.getCurrentSession()
			.createSQLQuery(queryStr.toString())
			.addScalar("id", StandardBasicTypes.INTEGER)
			.addScalar("truckBrand", StandardBasicTypes.STRING)
			.addScalar("truckType", StandardBasicTypes.STRING)
			.addScalar("capacity", StandardBasicTypes.STRING)
			.addScalar("plateNo", StandardBasicTypes.STRING)
			.addScalar("truckLength", StandardBasicTypes.STRING)
			.addScalar("truckVolumn", StandardBasicTypes.FLOAT)
			.addScalar("bodyStruc", StandardBasicTypes.STRING)
			.addScalar("locProvince", StandardBasicTypes.STRING)
			.addScalar("locCity", StandardBasicTypes.STRING)
			.addScalar("locRegion", StandardBasicTypes.STRING)
			.addScalar("drivingLicense", StandardBasicTypes.STRING)
			.addScalar("runningToken", StandardBasicTypes.STRING)
			.addScalar("driverName", StandardBasicTypes.STRING)
			.addScalar("driverSex", StandardBasicTypes.INTEGER)
			.addScalar("mobileNo", StandardBasicTypes.STRING)
			.addScalar("licenseType", StandardBasicTypes.STRING)
			.addScalar("freqLine", StandardBasicTypes.STRING)
			.addScalar("makeTime", StandardBasicTypes.TIMESTAMP)
			.addScalar("modifyTime", StandardBasicTypes.TIMESTAMP)
			.addScalar("truckState", StandardBasicTypes.INTEGER)
			.addScalar("createUserId", StandardBasicTypes.LONG)
			.addScalar("createUserName", StandardBasicTypes.STRING)
			.addScalar("auditUserId", StandardBasicTypes.LONG)
			.addScalar("auditUserName", StandardBasicTypes.STRING)
			.addScalar("auditTime", StandardBasicTypes.TIMESTAMP)
			.addScalar("lat", StandardBasicTypes.DOUBLE)
			.addScalar("lng", StandardBasicTypes.DOUBLE)
			.addScalar("getTime", StandardBasicTypes.TIMESTAMP)
			.addScalar("cityCode", StandardBasicTypes.STRING)
			.addScalar("addressDetail", StandardBasicTypes.STRING)
			.addScalar("locationDesc", StandardBasicTypes.STRING)
			.setString("plateNo", plateNo)
			.setString("region",region)
			.setString("city", city)
			.setFirstResult((pager.getPage() -1) * pager.getRows())
			.setMaxResults(pager.getRows())
			.setResultTransformer(Transformers.aliasToBean(TruckInfoVo.class))
			.list();		
	}

	@Override
	public long getPagerTruckRecords(JQGridPager pager, String city,
			String region, String plateNo) {
		StringBuffer queryStr = new StringBuffer();
		queryStr.append("select count(*) ")
	        .append("  from TruckInfo a, DriverLocationrt b ")
	        .append(" where a.id = b.id")
	        .append("   and a.plateNo like :plateNo ")
	        .append("   and ( b.region = :region )")
	        .append("   and ( b.city = :city ) ");
		if (plateNo == null || plateNo.trim().length() == 0) {
			plateNo = "%";
		} else {
			plateNo = new StringBuffer().append(plateNo).append("%").toString();
		}
		
		return ((Number) sessionFactory.getCurrentSession()
			.createSQLQuery(queryStr.toString())
			.setString("plateNo", plateNo)
			.setString("region",region)
			.setString("city", city)
			.uniqueResult()).longValue();
	}
	
	
}
